<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2019/4/11
 * Time: 14:05
 */

namespace app\common\model;


use app\admin\model\Userlist;
use think\Model;

class Sign extends Model
{

    public function get_list(){

        $user = new User();
        $limit = input('limit');

        $user_id = input('user_id');

        $last_month = strtotime(date('Y',time()).'-'.(date('m',time())-1).'-01');
        $this_month = strtotime(date('Y-m-1 00:00:00',time()));
        $count_lastmonth = date('d',$this_month-1);
        $count_thismoth  = date('d',time());

        $bgtime = input('begin_time');
        $endtime = input('end_time');
        if($bgtime){
            $where['sign_time']=['>',strtotime($bgtime)];
        }
        if($endtime){
            $where['sign_time']=['<',strtotime($endtime)];
        }
        if($bgtime&&$endtime){
            $where['sign_time']=['between',[strtotime($bgtime),strtotime($endtime)]];
        }
        $count_def = round((strtotime($endtime)-strtotime($bgtime))/86400,0);
        //如果有user_id  则是需要筛选用户或中部门的信息
        if($user_id){
            //user_id 是数字则是筛选部门信息
            if(is_numeric($user_id)){
                $userlist = model('userlist')->where(['depart_id'=>$user_id])->column('id');
                $uwhere['user_id'] = ['in',$userlist];
            }else{
                $uwhere['user_id'] =$user_id;
            }
            $lastresult = $this->field('count(`id`) as count')->where(['sign_time'=>['between',[$last_month,$this_month-1]]])->where($uwhere)->find();
            $thisresult = $this->field('count(`id`) as count')->where($uwhere)->where(['sign_time'=>['>=',$this_month]])->find();
            $def = $this->field('count(`id`) as count')->where($where)->where($uwhere)->find();
            $user_list['data'][0]['lastmonth'] = round(($lastresult['count'] / $count_lastmonth)*100, 2) . '%';
            $user_list['data'][0]['count_lastmonth'] = $lastresult['count'];
            $user_list['data'][0]['thismonth'] = round(($thisresult['count'] / $count_thismoth)*100, 2) . '%';
            $user_list['data'][0]['count_thismonth'] = $thisresult['count'];
            $user_list['data'][0]['def'] = round(($def['count'] / $count_def)*100, 2) . '%';
            $user_list['data'][0]['count_def'] = $def['count'];
            $user_list['data'][0]['user_name'] = model('userlist')->where(['id'=>$user_id])->value('name');
            $user_list['current_page']=1;
            $user_list['last_page']=1;
            $user_list['per_page']=1;
            $user_list['total']=1;
        }else {
            //全部查询
            $user_list = $user->paginate($limit)->toArray();
            foreach ($user_list['data'] as $k => $v) {
                $lastresult = $this->field('count(`id`) as count')->where(['user_id' => $v['user_id'], 'sign_time' => ['between', [$last_month, $this_month-1]]])->find();
                $thisresult = $this->field('count(`id`) as count')->where(['user_id' => $v['user_id'], 'sign_time' => ['>=', $this_month]])->find();
                $def = $this->field('count(`id`) as count')->where(['user_id' => $v['user_id']])->where($where)->find();
                $user_list['data'][$k]['lastmonth'] = round(($lastresult['count'] / $count_lastmonth)*100, 2) . '%';
                $user_list['data'][$k]['count_lastmonth'] = $lastresult['count'];
                $user_list['data'][$k]['thismonth'] = round(($thisresult['count'] / $count_thismoth)*100, 2) . '%';
                $user_list['data'][$k]['count_thismonth'] = $thisresult['count'];
                $user_list['data'][$k]['def'] = round(($def['count'] / $count_def)*100, 2) . '%';
                $user_list['data'][$k]['count_def'] = $def['count'];
            }
        }
        $user_list['code'] = 0;//用于前台数据表格判断成功字段
        return $user_list;
    }

    public function output(){
        $user = new User();
       // $limit = input('limit');
        $user_list = $user->select();
        $bgtime = input('begin_time');
        $endtime = input('end_time');
        if($bgtime){
            $where['sign_time']=['>',strtotime($bgtime)];
        }
        if($endtime){
            $where['sign_time']=['<',strtotime($endtime)];
        }
        if($bgtime&&$endtime){
            $where['sign_time']=['between',[strtotime($bgtime),strtotime($endtime)]];
        }
        $count_def = round((strtotime($endtime)-strtotime($bgtime))/86400,0);

        $last_month = strtotime(date('Y',time()).'-'.(date('m',time())-1).'-01');
        $this_month = strtotime(date('Y-m-1 00:00:00',time()));
        $count_lastmonth = date('d',$this_month-1);
        $count_thismoth  = date('d',time());
        foreach ($user_list as $k=>$v){
            $lastresult = $this->field('count(`id`) as count')->where(['user_id'=>$v['user_id'],'sign_time'=>['between',[$last_month,($this_month-1)]]])->find();
            $thisresult = $this->field('count(`id`) as count')->where(['user_id'=>$v['user_id'],'sign_time'=>['>=',$this_month]])->find();
            $def = $this->field('count(`id`) as count')->where(['user_id'=>$v['user_id']])->where($where)->find();
            $user_list[$k]['lastmonth'] =round($lastresult['count']/$count_lastmonth,2).'%';
            $user_list[$k]['count_lastmonth'] =$lastresult['count'];
            $user_list[$k]['thismonth'] =round($thisresult['count']/$count_thismoth,2).'%';
            $user_list[$k]['count_thismonth'] =$thisresult['count'];
            $user_list['data'][$k]['def'] = round($def['count']/$count_def,2).'%';
            $user_list['data'][$k]['count_def'] = $def['count'];
        }

        include_once('./public/phpexcel/PHPExcel.php');
        $PHPExcel = new \PHPExcel();
        //填入表头
        $PHPExcel->getActiveSheet()->setCellValue('A1', '姓名');
        $PHPExcel->getActiveSheet()->setCellValue('B1', '上月签到率');
        $PHPExcel->getActiveSheet()->setCellValue('C1', '上月签到数');
        $PHPExcel->getActiveSheet()->setCellValue('D1', '本月签到率');
        $PHPExcel->getActiveSheet()->setCellValue('E1', '本月签到数');
        if($user_list){
            $i=2;
            foreach ($user_list as $k=>$v){
                $PHPExcel->getActiveSheet()->setCellValue('A'.$i, $v['user_name']);
                $PHPExcel->getActiveSheet()->setCellValue('B'.$i, $v['lastmonth']);
                $PHPExcel->getActiveSheet()->setCellValue('C'.$i, $v['count_lastmonth']);
                $PHPExcel->getActiveSheet()->setCellValue('D'.$i, $v['thismonth']);
                $PHPExcel->getActiveSheet()->setCellValue('E'.$i, $v['count_thismonth']);
                $i++;
            }
            $objWriter = new \PHPExcel_Writer_Excel2007($PHPExcel);
            header("Pragma: public");
            header("Expires: 0");
            header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
            header("Content-Type:application/force-download");
            header("Content-Type:application/vnd.ms-execl");
            header("Content-Type:application/octet-stream");
            header("Content-Type:application/download");
            header('Content-Disposition:attachment;filename="'.date('YmdHis').'.xlsx"');
            header("Content-Transfer-Encoding:binary");
            $objWriter->save('php://output');
        }else{
            exit("<script>alert('当前结果为空');history.go(-1);</script>");
        }
    }


    public function output_detail(){
        $bgtime = input('begin_time');
        $endtime = input('end_time');
        if($bgtime){
            $where['sign_time']=['>',strtotime($bgtime)];
        }
        if($endtime){
            $where['sign_time']=['<',strtotime($endtime)];
        }
        if($bgtime&&$endtime){
            $where['sign_time']=['between',[strtotime($bgtime),strtotime($endtime)]];
        }
        $list =$this->where($where)->order(['sign_time desc'])->select();
        $user = \model('user')->column('user_name','user_id');
        include_once('./public/phpexcel/PHPExcel.php');
        $PHPExcel = new \PHPExcel();
        //填入表头
        $PHPExcel->getActiveSheet()->setCellValue('A1', '用户姓名');
        $PHPExcel->getActiveSheet()->setCellValue('B1', '签到时间');
        if($list){
            $i=2;
            foreach ($list as $k=>$v){
                $PHPExcel->getActiveSheet()->setCellValue('A'.$i, $user[$v['user_id']]);
                $PHPExcel->getActiveSheet()->setCellValue('B'.$i, date('Y-m-d H:i:s',$v['sign_time']));
                $i++;
            }
            $objWriter = new \PHPExcel_Writer_Excel2007($PHPExcel);
            header("Pragma: public");
            header("Expires: 0");
            header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
            header("Content-Type:application/force-download");
            header("Content-Type:application/vnd.ms-execl");
            header("Content-Type:application/octet-stream");
            header("Content-Type:application/download");
            header('Content-Disposition:attachment;filename="'.date('YmdHis').'.xlsx"');
            header("Content-Transfer-Encoding:binary");
            $objWriter->save('php://output');
        }else{
            exit("<script>alert('当前结果为空');history.go(-1);</script>");
        }
    }
}